//The MIT License
//
//Copyright (c) 2009 nodchip
//
//Permission is hereby granted, free of charge, to any person obtaining a copy
//of this software and associated documentation files (the "Software"), to deal
//in the Software without restriction, including without limitation the rights
//to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//copies of the Software, and to permit persons to whom the Software is
//furnished to do so, subject to the following conditions:
//
//The above copyright notice and this permission notice shall be included in
//all copies or substantial portions of the Software.
//
//THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//THE SOFTWARE.
package tv.dyndns.kishibe.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import tv.dyndns.kishibe.client.Constant;
import tv.dyndns.kishibe.client.packet.PacketBbsResponse;
import tv.dyndns.kishibe.client.packet.PacketBbsThread;
import tv.dyndns.kishibe.client.packet.PacketChatData;
import tv.dyndns.kishibe.client.packet.PacketLinkData;
import tv.dyndns.kishibe.client.packet.PacketProblemCreationLog;
import tv.dyndns.kishibe.client.packet.PacketProblemData;
import tv.dyndns.kishibe.client.packet.PacketProblemDataMinimum;
import tv.dyndns.kishibe.client.packet.PacketRankingData;
import tv.dyndns.kishibe.client.packet.PacketUserData;
import tv.dyndns.kishibe.client.packet.PacketWrongAnswer;
import tv.dyndns.kishibe.server.ProblemManager.PacketProblemDataMinimums;
import tv.dyndns.kishibe.server.relevance.SentencesProcessor;

public class Database {
	private final DatabaseConnectionPool connectionPool = new DatabaseConnectionPool();

	protected Database() {
	}

	public void addProblemIdsToReport(final int userCode, final int[] problemIds) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("INSERT IGNORE INTO report_problem (USER_CODE, PROBLEM_ID) VALUES (?, ?)");
			statement.setInt(1, userCode);
			for (int problemId : problemIds) {
				statement.setInt(2, problemId);
				statement.addBatch();
			}
			statement.executeBatch();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void removeProblemIdFromReport(int userCode, int problemID) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("DELETE IGNORE FROM report_problem WHERE USER_CODE = ? AND PROBLEM_ID = ?");
			statement.setInt(1, userCode);
			statement.setInt(2, problemID);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void clearProblemIdFromReport(int userCode) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("DELETE IGNORE FROM report_problem WHERE USER_CODE = ?");
			statement.setInt(1, userCode);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public PacketProblemData[] getUserProblemReport(int userCode) {
		PacketProblemData[] problemDatas = null;
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE ID IN (SELECT PROBLEM_ID FROM report_problem WHERE USER_CODE = ?)");
			statement.setInt(1, userCode);
			problemDatas = getPacketProblemDatas(statement);
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return problemDatas;
	}

	private PacketProblemData[] getPacketProblemDatas(PreparedStatement statement) throws SQLException {
		final ResultSet resultSet = statement.executeQuery();
		resultSet.last();
		int numberOfRow = Math.max(resultSet.getRow(), 0);
		resultSet.beforeFirst();

		final PacketProblemData[] datas = new PacketProblemData[numberOfRow];
		for (int index = 0; resultSet.next() && index < numberOfRow; ++index) {
			final PacketProblemData data = new PacketProblemData();
			data.problemId = resultSet.getInt("problem.ID");
			data.sentence = resultSet.getString("problem.SENTENCE");
			data.note = resultSet.getString("problem.NOTE");
			data.good = resultSet.getInt("problem.GOOD");
			data.bad = resultSet.getInt("problem.BAD");
			data.genre = resultSet.getInt("problem.GENRE");
			data.type = resultSet.getInt("problem.TYPE");
			data.randomFlag = resultSet.getInt("problem.RANDOM_FLAG");
			data.answer = new String[4];
			data.answer[0] = resultSet.getString("problem.ANSWER0");
			data.answer[1] = resultSet.getString("problem.ANSWER1");
			data.answer[2] = resultSet.getString("problem.ANSWER2");
			data.answer[3] = resultSet.getString("problem.ANSWER3");
			data.creator = resultSet.getString("problem.CREATER");
			data.voteGood = resultSet.getInt("problem.VOTE_GOOD");
			data.voteBad = resultSet.getInt("problem.VOTE_BAD");
			datas[index] = data;
		}
		resultSet.close();
		statement.close();
		return datas;
	}

	public PacketUserData getUserData(int userCode) {
		PacketUserData userData = null;
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT * FROM player WHERE USER_CODE = ?");
			statement.setInt(1, userCode);
			ResultSet rs = statement.executeQuery();
			final PacketUserData data = new PacketUserData();
			data.initialize();
			while (rs.next()) {
				data.userCode = rs.getInt("USER_CODE");
				data.playerName = rs.getString("NAME");
				data.greeting = rs.getString("GREETING");
				data.highScore = rs.getInt("HIGH_SCORE");
				data.averageScore = rs.getInt("AVERAGE_SCORE");
				data.playCount = rs.getInt("PLAY_COUNT");
				data.rating = rs.getInt("VICTORY_POINT");
				data.levelName = rs.getInt("LEVEL_NAME");
				data.levelNumber = rs.getInt("LEVEL_NUMBER");
				data.averageRank = rs.getFloat("AVERAGE_RANK");
				data.genre = rs.getInt("GENRE");
				data.type = rs.getInt("TYPE");
				data.classLevel = rs.getInt("CLASS_LEVEL");
				data.imageFileName = rs.getString("IMAGE_FILE_NAME");
				data.playSound = rs.getBoolean("PLAY_SOUND");
				data.multiGenre = rs.getBoolean("MULTI_GENRE");
				data.multiType = rs.getBoolean("MULTI_TYPE");
				data.difficultSelect = rs.getInt("DIFFICULT_SELECT");
				data.rankingMove = rs.getBoolean("RANKING_MOVE");
				data.bbsDispInfo = rs.getInt("BBS_DISP_INFO");
				data.bbsAge = rs.getBoolean("BBS_AGE");
				data.timerMode = rs.getInt("TIMER_MODE");
				data.prefecture = rs.getInt("PREFECTURE");
				data.chat = rs.getBoolean("CHAT");
				data.newProblem = rs.getBoolean("NEW_PROBLEM");
				data.publicEvent = rs.getBoolean("PUBLIC_EVENT");
			}
			rs.close();
			statement.close();

			// 個人正解率
			data.correctCount = new int[Constant.NUMBER_OF_GENRE][Constant.NUMBER_OF_TYPE_WITH_RANDOM][2];

			statement = connection.prepareStatement("SELECT * FROM correct_count WHERE USER_CODE = ?");
			statement.setInt(1, data.userCode);
			rs = statement.executeQuery();
			while (rs.next()) {
				final int genre = rs.getInt("GENRE");
				final int type = rs.getInt("TYPE");
				final int good = rs.getInt("GOOD");
				final int bad = rs.getInt("BAD");
				if (genre >= Constant.NUMBER_OF_GENRE || type >= Constant.NUMBER_OF_TYPE_WITH_RANDOM) {
					continue;
				}
				data.correctCount[genre][type][0] = good;
				data.correctCount[genre][type][1] = bad;
			}
			rs.close();
			statement.close();

			// 無視ユーザーコード
			statement = connection.prepareStatement("SELECT TARGET_USER_CODE FROM ignore_id WHERE USER_CODE = ?");
			statement.setInt(1, userCode);
			rs = statement.executeQuery();
			data.ignoreUserCodes = new HashSet<Integer>();
			while (rs.next()) {
				final int targetUserCode = rs.getInt("TARGET_USER_CODE");
				data.ignoreUserCodes.add(targetUserCode);
			}
			data.ignoreUserCodes.addAll(CachedDatabase.getInstance().getServerIgnoreUserCode().keySet());
			rs.close();
			statement.close();

			userData = data;
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return userData;
	}

	public void setUserData(PacketUserData data) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			{
				Statement statement = connection.createStatement();
				statement.execute("START TRANSACTION");
				statement.close();
			}
			{
				PreparedStatement statement = connection.prepareStatement("REPLACE INTO player (USER_CODE, NAME, GREETING, HIGH_SCORE, AVERAGE_SCORE, PLAY_COUNT, VICTORY_POINT, LEVEL_NAME, LEVEL_NUMBER, AVERAGE_RANK, GENRE, TYPE, CLASS_LEVEL, IMAGE_FILE_NAME, PLAY_SOUND, MULTI_GENRE, MULTI_TYPE, DIFFICULT_SELECT, RANKING_MOVE, LAST_LOGIN, BBS_DISP_INFO, BBS_AGE, TIMER_MODE, PREFECTURE, CHAT, NEW_PROBLEM, PUBLIC_EVENT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				statement.setInt(1, data.userCode);
				statement.setString(2, data.playerName != null ? data.playerName : "");
				statement.setString(3, data.greeting != null ? data.greeting : "");
				statement.setInt(4, data.highScore);
				statement.setInt(5, data.averageScore);
				statement.setInt(6, data.playCount);
				statement.setInt(7, data.rating);
				statement.setInt(8, data.levelName);
				statement.setInt(9, data.levelNumber);
				statement.setFloat(10, data.averageRank);
				statement.setInt(11, data.genre);
				statement.setInt(12, data.type);
				statement.setInt(13, data.classLevel);
				statement.setString(14, (data.imageFileName != null && data.imageFileName.length() != 0) ? data.imageFileName : Constant.ICON_NO_IMAGE);
				statement.setBoolean(15, data.playSound);
				statement.setBoolean(16, data.multiGenre);
				statement.setBoolean(17, data.multiType);
				statement.setInt(18, data.difficultSelect);
				statement.setBoolean(19, data.rankingMove);
				statement.setTimestamp(20, new Timestamp(System.currentTimeMillis()));
				statement.setInt(21, data.bbsDispInfo);
				statement.setBoolean(22, data.bbsAge);
				statement.setInt(23, data.timerMode);
				statement.setInt(24, data.prefecture);
				statement.setBoolean(25, data.chat);
				statement.setBoolean(26, data.newProblem);
				statement.setBoolean(27, data.publicEvent);
				statement.executeUpdate();
				statement.close();

				statement = connection.prepareStatement("REPLACE INTO correct_count (USER_CODE, GENRE, TYPE, GOOD, BAD) VALUE (?, ?, ?, ?, ?)");
				statement.setInt(1, data.userCode);
				for (int genre = 0; genre < Constant.NUMBER_OF_GENRE; ++genre) {
					statement.setInt(2, genre);
					for (int type = 0; type < Constant.NUMBER_OF_TYPE_WITH_RANDOM; ++type) {
						statement.setInt(3, type);
						statement.setInt(4, data.correctCount[genre][type][0]);
						statement.setInt(5, data.correctCount[genre][type][1]);
						statement.addBatch();
					}
				}
				statement.executeBatch();
				statement.close();
			}
			{
				Statement statement = connection.createStatement();
				statement.execute("COMMIT");
				statement.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	private static final String KEY_SESSION = "SESSION";
	private static final String KEY_PLAY = "PLAY";
	private static final String KEY_PAGE_VIEW = "page_view";

	public PageView loadPageView() {
		final PageView pageView = new PageView();
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT * FROM page_view");
			final ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				final String type = resultSet.getString("TYPE");
				final int number = resultSet.getInt("COUNT");

				if (type.equals(KEY_SESSION)) {
					pageView.session = number;
				} else if (type.equals(KEY_PLAY)) {
					pageView.play = number;
				} else if (type.equals(KEY_PAGE_VIEW)) {
					pageView.pageView = number;
				}
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return pageView;
	}

	public void savePageView(PageView pageView) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("REPLACE INTO page_view (TYPE, COUNT) VALUES (?, ?)");

			statement.setString(1, KEY_SESSION);
			statement.setInt(2, pageView.session);
			statement.addBatch();

			statement.setString(1, KEY_PLAY);
			statement.setInt(2, pageView.play);
			statement.addBatch();

			statement.setString(1, KEY_PAGE_VIEW);
			statement.setInt(2, pageView.pageView);
			statement.addBatch();

			statement.executeBatch();

			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public List<PacketProblemDataMinimum> getMinimumProblemDatas() {
		List<PacketProblemDataMinimum> result = null;
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT ID, GENRE, TYPE, GOOD, BAD, RANDOM_FLAG FROM problem");

			final ResultSet resultSet = statement.executeQuery();
			final List<PacketProblemDataMinimum> datas = new ArrayList<PacketProblemDataMinimum>();
			while (resultSet.next()) {
				final PacketProblemDataMinimum data = new PacketProblemDataMinimum();
				data.problemId = resultSet.getInt("ID");
				data.genre = resultSet.getInt("GENRE");
				data.type = resultSet.getInt("TYPE");
				data.good = resultSet.getInt("GOOD");
				data.bad = resultSet.getInt("BAD");
				data.randomFlag = resultSet.getInt("RANDOM_FLAG");
				datas.add(data);
			}

			resultSet.close();
			statement.close();

			result = datas;
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public PacketProblemDataMinimums getMinimumProblemDatasForThemeMode(String[] queries) {
		PacketProblemDataMinimums problemDatas = null;

		final String sql = ("SELECT * FROM problem WHERE MATCH (SENTENCE) AGAINST (?) ORDER BY ID ASC");

		Connection connection = null;

		try {
			final Map<Integer, PacketProblemDataMinimum> problems = new HashMap<Integer, PacketProblemDataMinimum>();

			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement(sql);
			for (String query : queries) {
				statement.setString(1, query);

				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					final PacketProblemDataMinimum data = new PacketProblemDataMinimum();
					data.problemId = resultSet.getInt("ID");
					data.genre = resultSet.getInt("GENRE");
					data.type = resultSet.getInt("TYPE");
					data.good = resultSet.getInt("GOOD");
					data.bad = resultSet.getInt("BAD");
					data.randomFlag = resultSet.getInt("RANDOM_FLAG");
					problems.put(data.problemId, data);
				}

				resultSet.close();
			}

			statement.close();

			problemDatas = new PacketProblemDataMinimums();
			problemDatas.addAll(problems.values());
			Collections.sort(problemDatas, new Comparator<PacketProblemDataMinimum>() {
				public int compare(PacketProblemDataMinimum o1, PacketProblemDataMinimum o2) {
					return o1.problemId - o2.problemId;
				}
			});

		} catch (SQLException e) {
			e.printStackTrace();
		}

		close(connection);

		return problemDatas;
	}

	private final Object lockAddProblemData = new Object();

	// 戻り値は問題番号
	public int addProblemData(PacketProblemData data) {
		synchronized (lockAddProblemData) {
			int result = Integer.MIN_VALUE;

			Connection connection = null;
			try {
				connection = connectionPool.getConnection();
				final String[] answer = new String[4];
				final String[] choice = new String[4];
				for (int i = 0; i < 4; ++i) {
					answer[i] = choice[i] = "";

					if (data.answer != null && i < data.answer.length && data.answer[i] != null) {
						answer[i] = data.answer[i];
					}
					if (data.choice != null && i < data.choice.length && data.choice[i] != null) {
						choice[i] = data.choice[i];
					}
				}

				PreparedStatement statement = connection.prepareStatement("START TRANSACTION");
				statement.execute();
				statement.close();

				statement = connection.prepareStatement("SELECT MAX(ID) FROM problem");
				final ResultSet resultSet = statement.executeQuery();
				int problemId = Integer.MIN_VALUE;
				while (resultSet.next()) {
					problemId = resultSet.getInt(1) + 1;
				}
				resultSet.close();
				statement.close();
				data.problemId = problemId;

				statement = connection.prepareStatement("INSERT INTO problem (ID, GENRE, TYPE, SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, GOOD, BAD, CREATER, NOTE, IMAGE_ANSWER, IMAGE_CHOICE, RANDOM_FLAG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				statement.setInt(1, data.problemId);
				statement.setInt(2, data.genre);
				statement.setInt(3, data.type);
				statement.setString(4, data.sentence);
				for (int i = 0; i < 4; ++i) {
					statement.setString(5 + i, answer[i]);
					statement.setString(9 + i, choice[i]);
				}
				statement.setInt(13, data.good);
				statement.setInt(14, data.bad);
				statement.setString(15, data.creator);
				statement.setString(16, data.note);
				statement.setBoolean(17, data.imageAnswer);
				statement.setBoolean(18, data.imageChoice);
				statement.setInt(19, data.randomFlag);
				statement.executeUpdate();
				statement.close();

				statement = connection.prepareStatement("COMMIT");
				statement.execute();
				statement.close();

				result = problemId;
			} catch (Exception e) {
				e.printStackTrace();
			}

			close(connection);

			return result;
		}
	}

	public void updateProblemData(PacketProblemData data) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final String[] answer = new String[4];
			final String[] choice = new String[4];
			for (int i = 0; i < 4; ++i) {
				answer[i] = choice[i] = "";

				if (data.answer != null && i < data.answer.length && data.answer[i] != null) {
					answer[i] = data.answer[i];
				}
				if (data.choice != null && i < data.choice.length && data.choice[i] != null) {
					choice[i] = data.choice[i];
				}
			}

			final PreparedStatement statement = connection.prepareStatement("UPDATE problem SET GENRE = ?, TYPE = ?, SENTENCE = ?, ANSWER0 = ?, ANSWER1 = ?, ANSWER2 = ?, ANSWER3 = ?, CHOICE0 = ?, CHOICE1 = ?, CHOICE2 = ?, CHOICE3 = ?, GOOD = ?, BAD = ?, CREATER = ?, NOTE = ?, IMAGE_ANSWER = ?, IMAGE_CHOICE = ?, RANDOM_FLAG = ?, VOTE_GOOD = ?, VOTE_BAD = ? WHERE ID = ?");
			statement.setInt(1, data.genre);
			statement.setInt(2, data.type);
			statement.setString(3, data.sentence);
			for (int i = 0; i < 4; ++i) {
				statement.setString(4 + i, answer[i]);
				statement.setString(8 + i, choice[i]);
			}
			statement.setInt(12, data.good);
			statement.setInt(13, data.bad);
			statement.setString(14, data.creator);
			statement.setString(15, data.note);
			statement.setBoolean(16, data.imageAnswer);
			statement.setBoolean(17, data.imageChoice);
			statement.setInt(18, data.randomFlag);
			statement.setInt(19, data.voteGood);
			statement.setInt(20, data.voteBad);
			statement.setInt(21, data.problemId);

			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void updateMinimumProblemData(PacketProblemDataMinimum data) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("UPDATE problem SET GENRE = ?, TYPE = ?, GOOD = ?, BAD = ?, RANDOM_FLAG = ? WHERE ID = ?");
			statement.setInt(1, data.genre);
			statement.setInt(2, data.type);
			statement.setInt(3, data.good);
			statement.setInt(4, data.bad);
			statement.setInt(5, data.randomFlag);
			statement.setInt(6, data.problemId);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public PacketProblemData[] getProblemData(List<Integer> ids) {
		PacketProblemData[] problemDatas = null;
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final List<PacketProblemData> datas = new ArrayList<PacketProblemData>(Constant.MAX_PROBLEMS_PER_SESSION);

			final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE ID = ?");
			for (int id : ids) {
				statement.setInt(1, id);
				final ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					final PacketProblemData data = new PacketProblemData();
					data.problemId = resultSet.getInt("ID");
					data.genre = resultSet.getInt("GENRE");
					data.type = resultSet.getInt("TYPE");
					data.sentence = resultSet.getString("SENTENCE");
					data.answer = new String[4];
					data.choice = new String[4];
					for (int i = 0; i < 4; ++i) {
						data.answer[i] = resultSet.getString("ANSWER" + i);
						data.choice[i] = resultSet.getString("CHOICE" + i);
					}
					data.good = resultSet.getInt("GOOD");
					data.bad = resultSet.getInt("BAD");
					data.creator = resultSet.getString("CREATER");
					data.note = resultSet.getString("NOTE");
					data.imageAnswer = resultSet.getBoolean("IMAGE_ANSWER");
					data.imageChoice = resultSet.getBoolean("IMAGE_CHOICE");
					data.randomFlag = resultSet.getInt("RANDOM_FLAG");
					data.voteGood = resultSet.getInt("VOTE_GOOD");
					data.voteBad = resultSet.getInt("VOTE_BAD");

					// この書き方馬鹿じゃね？
					final int permutations[][] = { Utility.getInstance().makePermutationArray(data.getNumberOfAnswer()), Utility.getInstance().makePermutationArray(data.getNumberOfChoice()) };
					data.createRandomizedData(permutations);

					datas.add(data);
				}
				resultSet.close();
			}
			statement.close();

			problemDatas = datas.toArray(new PacketProblemData[0]);
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return problemDatas;
	}

	public PacketProblemData[] searchProblem(String query, String creater, int genre, int type, int randomFlag) {
		final boolean queryEmpty = (query == null || query.isEmpty());
		final boolean createrEmpty = (creater == null || creater.isEmpty());
		if (queryEmpty && createrEmpty && genre <= 1 && type <= 1) {
			return new PacketProblemData[0];
		}

		if (genre == 0 || (genre & 1) == 1) {
			genre = (1 << Constant.NUMBER_OF_GENRE) - 1;
		}
		if (type == 0 || (type & 1) == 1) {
			type = (1 << Constant.NUMBER_OF_TYPE) - 1;
		}
		if (randomFlag == 0 || (randomFlag & 1) == 1) {
			randomFlag = (1 << Constant.NUMBER_OF_RANDOM) - 1;
		}

		PacketProblemData[] problemDatas = null;

		StringBuilder b = new StringBuilder();
		b.append("SELECT * FROM problem WHERE ");

		// 問題文
		if (!queryEmpty) {
			b.append("MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (?) AND ");
		}

		// 作成者
		if (!createrEmpty) {
			b.append("MATCH (CREATER) AGAINST (?) AND ");
		}

		// ジャンル
		b.append("GENRE IN (");
		b.append(createCommaSeparated(genre, Constant.NUMBER_OF_GENRE));

		// 出題形式
		b.append(") AND TYPE IN (");
		b.append(createCommaSeparated(type, Constant.NUMBER_OF_TYPE_WITH_RANDOM));

		// ランダムフラグ
		b.append(") AND RANDOM_FLAG IN (");
		b.append(createCommaSeparated(randomFlag, Constant.NUMBER_OF_RANDOM));

		b.append(") ORDER BY ID ASC");

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement(b.toString());

			int index = 1;
			if (!queryEmpty) {
				statement.setString(index++, query);
			}
			if (!createrEmpty) {
				statement.setString(index++, creater);
			}

			problemDatas = getPacketProblemDatas(statement);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		close(connection);

		return problemDatas;
	}

	private String createCommaSeparated(int flag, int length) {
		final StringBuilder b = new StringBuilder();
		boolean first = true;
		for (int index = 0; index < length; ++index) {
			if ((flag & (1 << index)) == 0) {
				continue;
			}

			if (first) {
				first = false;
			} else {
				b.append(',');
			}
			b.append(index);
		}
		return b.toString();
	}

	public PacketProblemData[] searchSimilarProblemData(String query) {
		query = query.replaceAll("[\\p{ASCII}]", "");
		{
			// 文章を3文字づつ分解する
			final int length = query.length();
			if (length > 3) {
				final StringBuilder b = new StringBuilder(length);
				for (int i = 0; i + 3 <= length; ++i) {
					if (i != 0) {
						b.append(' ');
					}
					b.append(query.substring(i, i + 3));
				}
				query = b.toString();
			}
		}

		PacketProblemData[] problemDatas = null;
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (? IN BOOLEAN MODE) ORDER BY MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (? IN BOOLEAN MODE) DESC LIMIT 0, 10");

			statement.setString(1, query);
			statement.setString(2, query);
			problemDatas = getPacketProblemDatas(statement);
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return problemDatas;
	}

	public PacketProblemData[] searchProblemDataByCreater(String creater) {
		PacketProblemData[] packetProblemDatas = null;
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem WHERE MATCH (CREATER) AGAINST (?) ORDER BY ID ASC");
			statement.setString(1, creater);
			packetProblemDatas = getPacketProblemDatas(statement);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		close(connection);

		return packetProblemDatas;
	}

	public void addPlayerAnswers(int problemID, int type, List<String> answers) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("START TRANSACTION");
			statement.execute();
			statement.close();

			statement = connection.prepareStatement("INSERT DELAYED player_answer (PROBLEM_ID, ANSWER, COUNT) VALUES (?, ?, 1) ON DUPLICATE KEY UPDATE COUNT = COUNT + 1");
			statement.setInt(1, problemID);
			for (String answer : answers) {
				if (answer.isEmpty()) {
					continue;
				}

				// 線結びの回答整理
				if (type == Constant.TYPE_SENMUSUBI || type == Constant.TYPE_TATO) {
					final String[] split = answer.split(Constant.DELIMITER_GENERAL);
					Arrays.sort(split);

					final StringBuilder builder = new StringBuilder();
					for (String s : split) {
						builder.append(s);
						builder.append(Constant.DELIMITER_GENERAL);
					}
					while (builder.charAt(builder.length() - 1) == Constant.DELIMITER_GENERAL.charAt(0)) {
						builder.deleteCharAt(builder.length() - 1);
					}
					answer = builder.toString();
				}

				statement.setString(2, answer);
				statement.addBatch();
			}
			statement.executeBatch();
			statement.close();

			statement = connection.prepareStatement("COMMIT");
			statement.execute();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public PacketWrongAnswer[] getPlayerAnswers(int problemID) {
		PacketWrongAnswer[] wrongAnswers = null;
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final List<PacketWrongAnswer> answers = new ArrayList<PacketWrongAnswer>();
			final PreparedStatement statement = connection.prepareStatement("SELECT ANSWER, COUNT FROM player_answer WHERE PROBLEM_ID = ? ORDER BY COUNT DESC");
			statement.setInt(1, problemID);
			final ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				final PacketWrongAnswer wrongAnswer = new PacketWrongAnswer();
				wrongAnswer.answer = resultSet.getString("ANSWER");
				wrongAnswer.count = resultSet.getInt("COUNT");
				answers.add(wrongAnswer);
			}
			resultSet.close();
			statement.close();
			wrongAnswers = answers.toArray(new PacketWrongAnswer[0]);
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return wrongAnswers;
	}

	public void removePlayerAnswers(int problemID) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("DELETE FROM player_answer WHERE PROBLEM_ID = ?");
			statement.setInt(1, problemID);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public PacketRankingData[][] getRankingData() {
		final PacketRankingData[][] result = new PacketRankingData[Constant.NUMBER_OF_RANKING_TYPE][];
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			result[Constant.RANKING_HIGH_SCORE] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, HIGH_SCORE AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
			result[Constant.RANKING_AVERAGE_SCORE] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, AVERAGE_SCORE AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
			result[Constant.RANKING_PLAY_COUNT] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, PLAY_COUNT AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
			result[Constant.RANKING_VICTORY_POINT] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, VICTORY_POINT AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
			result[Constant.RANKING_AVERAGE_RANK] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, AVERAGE_RANK AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA ASC LIMIT 0, ?");
			result[Constant.RANKING_CLASS] = getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, CLASS_LEVEL AS DATA FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
			result[Constant.RANKING_CORRECT_RATIO] = getRankingDataFromResultSet(connection, "SELECT player.NAME, player.IMAGE_FILE_NAME, correct_count.GOOD / (correct_count.GOOD + correct_count.BAD) AS DATA FROM player, correct_count " + "WHERE player.USER_CODE = correct_count.USER_CODE AND correct_count.GENRE = 0 AND correct_count.TYPE = 0 AND player.PLAY_COUNT >= 10 AND player.LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?");
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public Map<String, PacketRankingData[]> getThemeModeRankingData() {
		final Map<String, PacketRankingData[]> result = new HashMap<String, PacketRankingData[]>();
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();

			for (String theme : getThemeModeQuery().keySet()) {
				result.put(theme, getRankingDataFromResultSet(connection, "SELECT NAME, IMAGE_FILE_NAME, SCORE AS DATA FROM player, theme_mode_score WHERE theme_mode_score.theme like '" + theme + "' AND player.USER_CODE = theme_mode_score.USER_CODE AND player.PLAY_COUNT >= 10 AND player.LAST_LOGIN >= ? ORDER BY DATA DESC LIMIT 0, ?"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	private PacketRankingData[] getRankingDataFromResultSet(Connection connection, String sql) throws Exception {
		final PreparedStatement statement = connection.prepareStatement(sql);
		final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
		statement.setTimestamp(1, new Timestamp(time));
		statement.setInt(2, Constant.NUMBER_OF_RANKING_DATA);

		final ResultSet resultSet = statement.executeQuery();
		resultSet.last();
		final int numberOfResut = resultSet.getRow();
		resultSet.beforeFirst();

		final PacketRankingData[] rankingDatas = new PacketRankingData[numberOfResut];
		int i = 0;
		while (resultSet.next()) {
			final PacketRankingData rankingData = new PacketRankingData();
			rankingData.name = resultSet.getString("NAME");
			rankingData.imageFileName = resultSet.getString("IMAGE_FILE_NAME");
			rankingData.data = resultSet.getString("DATA");
			rankingDatas[i++] = rankingData;
		}
		resultSet.close();
		statement.close();

		return rankingDatas;
	}

	public void addCreationLog(int problemID, int userCode, String machineIP) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("INSERT DELAYED INTO creation_log (PROBLEM_ID, USER_CODE, DATE, MACHINE_IP) VALUES (?, ?, ?, ?)");
			statement.setInt(1, problemID);
			statement.setInt(2, userCode);
			statement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			statement.setString(4, machineIP);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void addIgnoreUserCode(int userCode, int targetUserCode) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("INSERT INTO ignore_id (USER_CODE, TARGET_USER_CODE) VALUES (?, ?)");
			statement.setInt(1, userCode);
			statement.setInt(2, targetUserCode);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void removeIgnoreUserCode(int userCode, int targetUserCode) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("DELETE FROM ignore_id WHERE USER_CODE = ? AND TARGET_USER_CODE = ?");
			statement.setInt(1, userCode);
			statement.setInt(2, targetUserCode);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void addServerIgnoreUserCode(int userCode) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("INSERT IGNORE INTO ignore_id (USER_CODE, TARGET_USER_CODE) VALUES (?, ?)");
			statement.setInt(1, 0);
			statement.setInt(2, userCode);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	private static final Object DUMMY = new Object();

	protected Map<Integer, Object> getServerIgnoreUserCode() {
		final Map<Integer, Object> userCodes = new HashMap<Integer, Object>();
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT TARGET_USER_CODE FROM ignore_id WHERE USER_CODE = ?");
			statement.setInt(1, 0);
			final ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				userCodes.put(rs.getInt("TARGET_USER_CODE"), DUMMY);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return userCodes;
	}

	public void addChatLog(PacketChatData data) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("INSERT DELAYED IGNORE INTO chat_log (DATE, NAME, BODY, CLASS_LEVEL, USER_CODE, MACHINE_IP) VALUES (?, ?, ?, ?, ?, ?)");
			statement.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
			statement.setString(2, data.name);
			statement.setString(3, data.body);
			statement.setInt(4, data.classLevel);
			statement.setInt(5, data.userCode);
			statement.setString(6, data.machineIp);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	protected Map<Integer, Object> getLimitedUserCodes() {
		final Map<Integer, Object> result = new HashMap<Integer, Object>();
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT * FROM limited_user_code");
			final ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				result.put(rs.getInt("USER_CODE"), DUMMY);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	protected Map<String, Object> getLimitedIps() {
		final Map<String, Object> result = new HashMap<String, Object>();
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("SELECT * FROM limited_ip");
			final ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				result.put(rs.getString("IP"), DUMMY);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public PacketProblemCreationLog[] getProblemCreationHistory(int problemId) {
		PacketProblemCreationLog[] result = null;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final List<PacketProblemCreationLog> list = new ArrayList<PacketProblemCreationLog>();
			final PreparedStatement statement = connection.prepareStatement("SELECT player.NAME, creation_log.USER_CODE, creation_log.DATE, creation_log.MACHINE_IP FROM player, creation_log WHERE creation_log.PROBLEM_ID = ? AND creation_log.USER_CODE = player.USER_CODE");
			statement.setInt(1, problemId);
			final ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				PacketProblemCreationLog history = new PacketProblemCreationLog();
				history.name = resultSet.getString("player.NAME");
				history.userCode = resultSet.getInt("creation_log.USER_CODE");
				history.date = new Date(resultSet.getTimestamp("creation_log.DATE").getTime());
				history.ip = resultSet.getString("creation_log.MACHINE_IP");
				list.add(history);
			}
			resultSet.close();
			statement.close();

			result = list.toArray(new PacketProblemCreationLog[0]);

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public PacketBbsThread[] getBbsThread(int start, int count) {
		PacketBbsThread[] result = null;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final List<PacketBbsThread> list = new ArrayList<PacketBbsThread>();
			final PreparedStatement statement = connection.prepareStatement("SELECT id, title FROM bbs_thread ORDER BY lastUpdate DESC LIMIT ?, ?");
			statement.setInt(1, start);
			statement.setInt(2, count);

			final ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				PacketBbsThread thread = new PacketBbsThread();
				thread.id = resultSet.getLong("id");
				thread.title = resultSet.getString("title");
				list.add(thread);
			}
			resultSet.close();
			statement.close();

			result = list.toArray(new PacketBbsThread[0]);
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public PacketBbsResponse[] getBbsResponses(int threadId, int count) {
		PacketBbsResponse[] result = null;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			final List<PacketBbsResponse> list = new ArrayList<PacketBbsResponse>();

			// 最新コメントcount個
			PreparedStatement statement = connection.prepareStatement("SELECT * FROM bbs_response WHERE threadId = ? ORDER BY id DESC LIMIT 0, ?");
			statement.setInt(1, threadId);
			statement.setInt(2, count);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				PacketBbsResponse response = new PacketBbsResponse();
				response.id = resultSet.getLong("id");
				response.threadId = resultSet.getLong("threadId");
				response.name = resultSet.getString("name");
				response.userCode = resultSet.getInt("userCode");
				response.machineIp = resultSet.getString("userCode");
				response.dispInfo = resultSet.getInt("dispInfo");
				response.postTime = resultSet.getLong("postTime");
				response.body = resultSet.getString("body");
				list.add(response);
			}
			resultSet.close();
			statement.close();

			// スレ立て時のコメント
			statement = connection.prepareStatement("SELECT * FROM bbs_response WHERE threadId = ? ORDER BY id ASC LIMIT 0, 1");
			statement.setInt(1, threadId);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				PacketBbsResponse response = new PacketBbsResponse();
				response.id = resultSet.getLong("id");
				response.threadId = resultSet.getLong("threadId");
				response.name = resultSet.getString("name");
				response.userCode = resultSet.getInt("userCode");
				response.machineIp = resultSet.getString("userCode");
				response.dispInfo = resultSet.getInt("dispInfo");
				response.postTime = resultSet.getLong("postTime");
				response.body = resultSet.getString("body");
				if (list.get(list.size() - 1).postTime != response.postTime) {
					list.add(response);
				}
			}
			resultSet.close();
			statement.close();

			Collections.reverse(list);
			result = list.toArray(new PacketBbsResponse[0]);
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public void buildBbsThread(PacketBbsThread thread, PacketBbsResponse response) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("START TRANSACTION");
			statement.execute();
			statement.close();

			statement = connection.prepareStatement("INSERT INTO bbs_thread (lastUpdate, title) VALUES (?, ?)");
			statement.setLong(1, System.currentTimeMillis());
			statement.setString(2, thread.title);
			statement.executeUpdate();
			statement.close();

			statement = connection.prepareStatement("SELECT LAST_INSERT_ID() FROM bbs_thread");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				response.threadId = resultSet.getLong(1);
			}
			resultSet.close();
			statement.close();

			if (response.threadId == -1) {
				throw new Exception("新規スレッドIDが正しく返されませんでした");
			}

			statement = connection.prepareStatement("INSERT INTO bbs_response (threadId, name, userCode, machineIp, dispInfo, postTime, body) VALUES (?, ?, ?, ?, ?, ?, ?)");
			statement.setLong(1, response.threadId);
			statement.setString(2, response.name);
			statement.setInt(3, response.userCode);
			statement.setString(4, response.machineIp);
			statement.setInt(5, response.dispInfo);
			statement.setLong(6, System.currentTimeMillis());
			statement.setString(7, response.body);
			statement.executeUpdate();
			statement.close();

			statement = connection.prepareStatement("COMMIT");
			statement.execute();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void writeToBbs(PacketBbsResponse response, boolean age) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("START TRANSACTION");
			statement.execute();
			statement.close();

			statement = connection.prepareStatement("INSERT INTO bbs_response (threadId, name, userCode, machineIp, dispInfo, postTime, body) VALUES (?, ?, ?, ?, ?, ?, ?)");
			statement.setLong(1, response.threadId);
			statement.setString(2, response.name);
			statement.setInt(3, response.userCode);
			statement.setString(4, response.machineIp);
			statement.setInt(5, response.dispInfo);
			statement.setLong(6, System.currentTimeMillis());
			statement.setString(7, response.body);
			statement.executeUpdate();
			statement.close();

			if (age) {
				statement = connection.prepareStatement("UPDATE bbs_thread SET lastUpdate = ? WHERE id = ?");
				statement.setLong(1, System.currentTimeMillis());
				statement.setLong(2, response.threadId);
				statement.executeUpdate();
				statement.close();
			}

			statement = connection.prepareStatement("COMMIT");
			statement.execute();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public int getNumberOfBbsThread() {
		int result = 0;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM bbs_thread");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				result = resultSet.getInt(1);
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public boolean isUsedUserCode(int userCode) {
		boolean result = false;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM player WHERE USER_CODE = ?");
			statement.setInt(1, userCode);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				result = resultSet.getInt(1) != 0;
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public void addLinkData(PacketLinkData linkData) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("INSERT INTO link (lastUpdate, homePageName, authorName, url, bannerUrl, description, userCode) VALUES (?, ?, ?, ?, ?, ?, ?)");
			statement.setLong(1, System.currentTimeMillis());
			statement.setString(2, linkData.homePageName);
			statement.setString(3, linkData.authorName);
			statement.setString(4, linkData.url);
			statement.setString(5, linkData.bannerUrl);
			statement.setString(6, linkData.description);
			statement.setInt(7, linkData.userCode);
			statement.execute();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void updateLinkData(PacketLinkData linkData) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("UPDATE link SET lastUpdate = ?, homePageName = ?, authorName = ?, url = ?, bannerUrl = ?, description = ?, userCode = ? WHERE id = ?");
			statement.setLong(1, System.currentTimeMillis());
			statement.setString(2, linkData.homePageName);
			statement.setString(3, linkData.authorName);
			statement.setString(4, linkData.url);
			statement.setString(5, linkData.bannerUrl);
			statement.setString(6, linkData.description);
			statement.setInt(7, linkData.userCode);
			statement.setInt(8, linkData.id);
			statement.execute();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void removeLinkData(int id) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("UPDATE link SET valid = FALSE WHERE id = ?");
			statement.setInt(1, id);
			statement.execute();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public PacketLinkData[] getLinkDatas(int start, int count) {
		PacketLinkData[] result = null;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT * FROM link WHERE valid = TRUE ORDER BY lastUpdate DESC LIMIT ?, ?");
			statement.setInt(1, start);
			statement.setInt(2, count);
			ResultSet resultSet = statement.executeQuery();
			List<PacketLinkData> list = new ArrayList<PacketLinkData>();
			while (resultSet.next()) {
				PacketLinkData linkData = new PacketLinkData();
				linkData.id = (int) resultSet.getLong("id");
				linkData.lastUpdate = resultSet.getLong("lastUpdate");
				linkData.homePageName = resultSet.getString("homePageName");
				linkData.authorName = resultSet.getString("authorName");
				linkData.url = resultSet.getString("url");
				linkData.bannerUrl = resultSet.getString("bannerUrl");
				linkData.description = resultSet.getString("description");
				linkData.userCode = resultSet.getInt("userCode");
				list.add(linkData);
			}
			resultSet.close();
			statement.close();

			result = list.toArray(new PacketLinkData[0]);
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public int getNumberOfLinkDatas() {
		int result = Integer.MIN_VALUE;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM link WHERE valid = TRUE");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				result = resultSet.getInt(1);
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	private void close(final Connection connection) {
		try {
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getNumberOfActiveUsers() {
		int result = Integer.MIN_VALUE;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ?");
			final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
			statement.setLong(1, time);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				result = resultSet.getInt(1);
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public Map<Integer, List<Integer>> getRatingGroupedByPrefecture() {
		final Map<Integer, List<Integer>> result = new HashMap<Integer, List<Integer>>();

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT VICTORY_POINT, PREFECTURE FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ? AND PREFECTURE != 0");
			final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
			statement.setLong(1, time);

			final ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				final int rating = resultSet.getInt("VICTORY_POINT");
				final int prefecture = resultSet.getInt("PREFECTURE");

				if (!result.containsKey(prefecture)) {
					result.put(prefecture, new ArrayList<Integer>());
				}
				result.get(prefecture).add(rating);
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public void addRatingHistory(int userCode, int rating) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("INSERT INTO rating_history (USER_CODE, TIME, RATING) VALUES (?, ?, ?)");
			statement.setInt(1, userCode);
			statement.setLong(2, System.currentTimeMillis());
			statement.setInt(3, rating);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public int[] getRatingHistory(int userCode) {
		int[] result = null;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT RATING FROM rating_history WHERE USER_CODE = ? ORDER BY TIME DESC LIMIT 0, ?");
			statement.setInt(1, userCode);
			statement.setInt(2, Constant.MAX_RATING_HISTORY);
			ResultSet resultSet = statement.executeQuery();
			resultSet.last();
			result = new int[resultSet.getRow()];
			resultSet.beforeFirst();
			int index = 0;
			while (resultSet.next()) {
				result[index++] = resultSet.getInt("RATING");
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public int[] getWholeRating() {
		int[] result = null;

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT VICTORY_POINT FROM player WHERE PLAY_COUNT >= 10 AND LAST_LOGIN >= ?");
			final long time = System.currentTimeMillis() - (long) Constant.RANKING_DISPLAY_DAY * 24 * 60 * 60 * 1000;
			statement.setLong(1, time);
			ResultSet resultSet = statement.executeQuery();
			resultSet.last();
			result = new int[resultSet.getRow()];
			resultSet.beforeFirst();
			int index = 0;
			while (resultSet.next()) {
				result[index++] = resultSet.getInt("VICTORY_POINT");
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public PacketProblemData[] getAdsenseProblems(String query) {
		final List<Integer> ids = new ArrayList<Integer>();

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT ID FROM problem WHERE MATCH (SENTENCE, ANSWER0, ANSWER1, ANSWER2, ANSWER3, CHOICE0, CHOICE1, CHOICE2, CHOICE3, NOTE) AGAINST (? IN BOOLEAN MODE)");
			statement.setString(1, query);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				ids.add(resultSet.getInt("ID"));
			}
			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return getProblemData(ids);
	}

	public Map<String, List<String>> getThemeModeQuery() {
		final Map<String, List<String>> queries = new HashMap<String, List<String>>();

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT * FROM theme_mode");
			ResultSet resultSet = statement.executeQuery();

			while (resultSet.next()) {
				final String theme = resultSet.getString("THEME");
				final String query = resultSet.getString("QUERY");

				if (!queries.containsKey(theme)) {
					queries.put(theme, new ArrayList<String>());
				}

				queries.get(theme).add(query);
			}

			resultSet.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return queries;
	}

	public void addThemeModeQuery(String theme, String query) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("INSERT theme_mode (THEME, QUERY) VALUES (?, ?)");
			statement.setString(1, theme);
			statement.setString(2, query);
			statement.execute();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void removeThemeModeQuery(String theme, String query) {
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
			PreparedStatement statement = connection.prepareStatement("DELETE FROM theme_mode WHERE THEME LIKE ? AND QUERY LIKE ?");
			statement.setString(1, theme);
			statement.setString(2, query);
			statement.execute();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void updateThemeModeScore(int userCode, String theme, int score) {
		Connection connection = null;

		try {
			connection = connectionPool.getConnection();
			final PreparedStatement statement = connection.prepareStatement("INSERT LOW_PRIORITY theme_mode_score (THEME, USER_CODE, SCORE) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE SCORE = ((SCORE < ?) * ? + (SCORE > ?) * SCORE);");
			statement.setString(1, theme);
			statement.setInt(2, userCode);
			statement.setInt(3, score);
			statement.setInt(4, score);
			statement.setInt(5, score);
			statement.setInt(6, score);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void voteToProblem(int problemId, boolean good, String feedback) {
		final int voteGood = good ? 1 : 0;
		final int voteBad = good ? 0 : 1;
		Connection connection = null;

		try {
			connection = connectionPool.getConnection();

			PreparedStatement statement = connection.prepareStatement("UPDATE problem SET VOTE_GOOD = VOTE_GOOD + ?, VOTE_BAD = VOTE_BAD + ? WHERE ID = ?;");
			statement.setInt(1, voteGood);
			statement.setInt(2, voteBad);
			statement.setInt(3, problemId);
			statement.executeUpdate();
			statement.close();

			statement = connection.prepareStatement("INSERT problem_questionnaire (problemId, text, date) VALUES (?, ?, ?)");
			statement.setInt(1, problemId);
			statement.setString(2, feedback);
			statement.setLong(3, System.currentTimeMillis());
			statement.executeUpdate();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public void clearProblemFeedback(int problemId) {
		Connection connection = null;

		try {
			connection = connectionPool.getConnection();

			PreparedStatement statement = connection.prepareStatement("UPDATE problem_questionnaire SET deleted = TRUE WHERE problemId = ?");
			statement.setInt(1, problemId);
			statement.executeUpdate();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public List<String> getProblemFeedback(int problemId) {
		Connection connection = null;

		final List<String> result = new ArrayList<String>();
		try {
			connection = connectionPool.getConnection();

			PreparedStatement statement = connection.prepareStatement("SELECT * FROM problem_questionnaire WHERE problemId = ? && deleted = FALSE");
			statement.setInt(1, problemId);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				result.add(resultSet.getString("text"));
			}
			resultSet.close();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	private static final String[] sentenceKeys = { "SENTENCE", "ANSWER0", "ANSWER1", "ANSWER2", "ANSWER3", "CHOICE0", "CHOICE1", "CHOICE2", "CHOICE3" };

	public void processProblems(SentencesProcessor sentencesProcessor) {
		System.err.println("Database.processProblems()");

		Connection connection = null;
		try {
			connection = connectionPool.getConnection();

			final StringBuilder sb = new StringBuilder();
			sb.append("SELECT ID");
			for (int i = 0; i < sentenceKeys.length; ++i) {
				sb.append(",");
				sb.append(sentenceKeys[i]);
			}
			sb.append(" FROM problem");

			final PreparedStatement statement = connection.prepareStatement(sb.toString());
			final ResultSet resultSet = statement.executeQuery();
			int counter = 0;
			final StringBuilder sentence = new StringBuilder();
			while (resultSet.next()) {
				sentence.delete(0, Integer.MAX_VALUE);

				final int problemId = resultSet.getInt("ID");
				for (String sentenceKey : sentenceKeys) {
					final String sentenceValue = resultSet.getString(sentenceKey);
					if (sentenceValue == null || sentenceValue.isEmpty()) {
						continue;
					}
					sentence.append(sentenceValue).append(" ");
				}

				sentencesProcessor.process(sentence.toString(), problemId);

				if (++counter % 10000 == 0) {
					System.err.println(counter);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}

	public PacketProblemData[] getLastestProblems() {
		final List<Integer> problemIds = new ArrayList<Integer>();

		Connection connection = null;

		try {
			connection = connectionPool.getConnection();

			PreparedStatement statement = connection.prepareStatement("SELECT ID FROM problem ORDER BY ID DESC LIMIT 0, 100");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				problemIds.add(resultSet.getInt("ID"));
			}
			resultSet.close();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		final List<PacketProblemData> problemDatas = new ArrayList<PacketProblemData>(Arrays.asList(getProblemData(problemIds)));
		Collections.sort(problemDatas, comparatorByProblemId);
		return problemDatas.toArray(new PacketProblemData[0]);
	}

	private final Comparator<PacketProblemData> comparatorByProblemId = new Comparator<PacketProblemData>() {
		@Override
		public int compare(PacketProblemData o1, PacketProblemData o2) {
			return o2.problemId - o1.problemId;
		}
	};

	public Map<Integer, Integer> getThemeModeEditorsStatus() {
		final Map<Integer, Integer> result = new HashMap<Integer, Integer>();
		Connection connection = null;

		try {
			connection = connectionPool.getConnection();

			PreparedStatement statement = connection.prepareStatement("SELECT * FROM theme_mode_editor");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				final int userCode = resultSet.getInt("userCode");
				final int status = resultSet.getInt("status");
				result.put(userCode, status);
			}
			resultSet.close();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);

		return result;
	}

	public void updateThemeModeEdtorsStatus(int userCode, int status) {
		Connection connection = null;

		try {
			connection = connectionPool.getConnection();

			PreparedStatement statement = connection.prepareStatement("REPLACE theme_mode_editor (userCode, status) VALUES (?, ?)");
			statement.setInt(1, userCode);
			statement.setInt(2, status);
			statement.executeUpdate();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		close(connection);
	}
}
